﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace App_Code
{
    /// <summary>
    /// Summary description for HotelDB
    /// </summary>
    public class HotelDB
    {
        static readonly string _connStr = String.Format("server={0};uid={1};pwd={2};database={3};Allow User Variables=True;",
                                             "anotherurl.com", "root", "rootpw!", "laostraveller");


        /*
         create table cityShortCode(csc_id int NOT NULL AUTO_INCREMENT, shortCode varchar(100) NOT NULL, name varchar(100) not null,
PRIMARY KEY (csc_id));
         * */


        public static void GetCityLookUp(Dictionary<string, string> records)
        {
            try
            {
                using (var conn = new MySqlConnection(_connStr))
                {
                    conn.Open();

                    const string sql = "select shortcode, name from cityshortcode order by name;";

                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        var myReader = cmd.ExecuteReader();
                        var ds = new DataSet();
                        ds.Load(myReader, LoadOption.PreserveChanges, new[] { "TABLE" });
                        myReader.Close();

                        foreach (DataRow row in ds.Tables["TABLE"].Rows)
                        {
                            records.Add((string)row[0], (string)row[1]);
                        }
                    }
                }
            }
            catch (Exception)
            {
            }
        }


        public static void GetHotels(Dictionary<int, HotelRecord> records)
        {
            try
            {
                using (var conn = new MySqlConnection(_connStr))
                {
                    conn.Open();

                    const string sql = "select ht_id, CITY, h_name, h_addr1, h_add2, h_tel, h_fax, h_email, h_web, place_id from hotel_tbl order by h_name, h_addr1, h_add2;";

                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        var myReader = cmd.ExecuteReader();
                        var ds = new DataSet();
                        ds.Load(myReader, LoadOption.PreserveChanges, new[] { "TABLE" });
                        myReader.Close();

                        foreach (DataRow row in ds.Tables["TABLE"].Rows)
                        {
                            records.Add((int)row[0], new HotelRecord
                            {
                                ID = (int)row[0],
                                CityShortCode = (string)row[1],
                                Name = (string)row[2],
                                AddressLine1 = (string)row[3],
                                AddressLine2 = (string)row[4],
                                Telephone = (string)row[5],
                                Fax = (string)row[6],
                                Email = (string)row[7],
                                WebURL = (string)row[8]
                            });
                        }
                    }
                }
            }
            catch (Exception)
            {
            }
        }
    }

    public class HotelRecord
    {
        public int ID { private get; set; }
        public string CityShortCode { get; set; }
        public string Name { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string Telephone { get; set; }
        public string Fax { get; set; }
        public string Email { get; set; } 
        public string WebURL { get; set;}

        public override string ToString()
        {
            return Name;
        }
    }
}